package dm

import (
	"bufio"
	"database/sql"
	"fmt"
	"os"
	"strings"

	_ "github.com/mattn/go-sqlite3" // 导入 SQLite 驱动
)

type User struct {
	Title    string // 类型
	UserName string // 账号
	PassWord string // 密码
}

type DatabaseManager struct {
	db *sql.DB // 数据库连接
}

func NewDatabaseManager(dbName string) (*DatabaseManager, error) {
	// 打开数据库连接
	db, err := sql.Open("sqlite3", dbName)
	if err != nil {
		return nil, err
	}

	err = db.Ping() // 检查数据库是否可连通
	if err != nil {
		return nil, err
	}

	dm := &DatabaseManager{
		db: db,
	}
	err = dm.initTable() // 初始化表格
	if err != nil {
		return nil, err
	}
	return dm, nil
}

func (dm *DatabaseManager) Close() error {
	return dm.db.Close() // 关闭数据库连接
}

func (dm *DatabaseManager) initTable() error {
	createTableSQL := `
	CREATE TABLE IF NOT EXISTS users (
		title TEXT,
		username TEXT,
		password TEXT
	);`
	_, err := dm.db.Exec(createTableSQL) // 执行创建表格的 SQL 语句
	return err
}

func (dm *DatabaseManager) Insert(user User) error {
	tx, err := dm.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	exists, err := dm.checkTitleExists(user.Title)
	if err != nil {
		return err
	}
	if exists {
		reader := bufio.NewReader(os.Stdin)
		fmt.Printf("类型 %s 已存在，是否更新(Y/N)?", user.Title)
		option, _ := reader.ReadString('\n')
		option = strings.ToLower(strings.TrimSpace(option))

		switch option {
		case "y":
			if err = dm.Update(user); err != nil {
				return err
			}
		case "n":
			fmt.Println("无事发生")
		default:
			return fmt.Errorf("无效的选项")
		}

		return nil
	}

	stmt, err := tx.Prepare(`
		INSERT INTO users (title, username, password) VALUES (?, ?, ?)
	`)
	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(user.Title, user.UserName, user.PassWord)
	if err != nil {
		return err
	}

	err = tx.Commit()
	if err != nil {
		return err
	}

	fmt.Println("保存成功")
	return nil
}

func (dm *DatabaseManager) Update(user User) error {
	tx, err := dm.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	stmt, err := tx.Prepare(`
		UPDATE users SET username = ?, password = ? WHERE title = ?
	`)
	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(user.UserName, user.PassWord, user.Title)
	if err != nil {
		return err
	}

	err = tx.Commit()
	if err != nil {
		return err
	}

	fmt.Printf("更新%s的信息成功\n", user.Title)
	return nil
}

func (dm *DatabaseManager) Delete(title string) error {
	tx, err := dm.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	stmt, err := tx.Prepare(`
		DELETE FROM users WHERE title = ?
	`)
	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(title)
	if err != nil {
		return err
	}

	err = tx.Commit()
	if err != nil {
		return err
	}

	fmt.Printf("删除%s的信息成功\n", title)
	return nil
}

func (dm *DatabaseManager) QueryList() ([]User, error) {
	rows, err := dm.db.Query(`
		SELECT title, username, password FROM users
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []User

	for rows.Next() {
		var user User
		err := rows.Scan(&user.Title, &user.UserName, &user.PassWord) // 读取查询结果中的数据到结构体
		if err != nil {
			return nil, err
		}
		users = append(users, user)
	}

	if err = rows.Err(); err != nil {
		return nil, err
	}

	return users, nil
}

func (dm *DatabaseManager) checkTitleExists(title string) (bool, error) {
	row := dm.db.QueryRow(`
		SELECT COUNT(*) FROM users WHERE title = ?
	`, title)

	var count int
	err := row.Scan(&count)
	if err != nil {
		return false, err
	}

	return count > 0, nil
}
